/**
 * 生成 SQL 入口函数
 * @param json
 */
export function doGenerateSQL(json: InputJSON, sqlType: SqlType) {
  // 如果不是查询sql 则走另外两个处理函数
  if (sqlType == SqlType.INSERT) {
    return doGenerateInstertSQL(json);
  } else if (sqlType === SqlType.UPDATE) {
    return doGenerateUpdateSQL(json);
  } else if (sqlType === SqlType.GEOMETRY) {
    return doGenerateGeometrySQL(json);
  }
  // 缺失入口
  if (!json?.main) {
    return null;
  }
  const sql = json.main.sql ?? json.main;
  if (!sql) {
    return null;
  }
  const initTreeNode = {
    title: "main",
    sql,
    children: [],
  };
  const rootInvokeTreeNode = { ...initTreeNode };
  const context = json;
  const resultSQL = generateSQL(
    "main",
    context,
    context.main?.params,
    rootInvokeTreeNode
  );
  return {
    resultSQL,
    invokeTree: rootInvokeTreeNode.children[0], // 取第一个作为根节点
  };
}
// 导出sql类型
export enum SqlType {
  INSERT = "insert",
  UPDATE = "update",
  SELECT = "select",
  GEOMETRY = "geometry",
};

/**
 * 生成updateSql
 * @param json
 */
function doGenerateUpdateSQL(json: any,) {
  const primarykey = json.primarykey
  const dataList = json.dataList
  const fields = json.fields
  const table = json.table
  // 校验
  let error = fieldCheck(json, ["primarykey", "dataList", "fields", "table"]);
  if (error) {
    return { error };
  }

  let resultSQL = `UPDATE ${json.table} SET `;
  // 拼接SET
  for (let i = 0; i < fields.length; i++) {
    let field = fields[i];
    /// 拼接开头
    resultSQL += `${field} = CASE ${primarykey}  `
    /// 拼接中间的 WHEN  THEN 部分
    for (let item of dataList) {
      // 要修改字段和主键都不为null才能拼接进去
      if (item[field] && item[primarykey]) {
        resultSQL += `WHEN ${item[primarykey]} THEN `
        if (!isNumber(item[field])) {
          // 如果是不是数字就加个单引号
          resultSQL += `'${item[field]}' `
        } else {
          resultSQL += `${item[field]} `
        }
      }
    }
    /// 拼接末尾
    resultSQL += i >= fields.length - 1 ? "END  " : "END,  ";
  }
  // 拼接WHERE
  resultSQL += `WHERE ${primarykey} IN (`;
  for (let i = 0; i < dataList.length; i++) {
    let item = dataList[i]
    if (item[primarykey]) {
      resultSQL += i >= dataList.length - 1 ? `${item[primarykey]} ` : `${item[primarykey]}, `
    }
  }
  resultSQL += ")";
  return {
    resultSQL,
    invokeTree: "", // 取第一个作为根节点
  };
}


/**
 * 生成insert Sql
 * @param json
 */
function doGenerateInstertSQL(json: any,) {
  const dataList = json.dataList;
  const fields = json.fields;
  const table = json.table;
  // 数据校验
  let error = fieldCheck(json, ["dataList", "fields", "table"]);
  if (error) {
    return { error };
  }
  // 拼接sql
  let resultSQL = `INSERT INTO ${table}(`;

  /// 拼接 字段
  fields.forEach((field: string, index: number) => {
    if (index >= fields.length - 1) {
      resultSQL += field;
    } else {
      resultSQL += `${field},`;
    }
  });
  /// 拼接values部分
  resultSQL += ") VALUES";

  dataList.forEach((item: any, index: number) => {
    resultSQL += "("
    fields.forEach((field: string, index: number) => {
      if (!isEmpty(item[field])) {
        if (isNumber(item[field])) { // 数字和字符串的拼接方式不一样
          resultSQL += `${item[field]}`;
        } else {
          resultSQL += `'${item[field]}'`;
        }
        // 补,
        if (index < fields.length - 1) {
          resultSQL += ",";
        }
      } else {
        resultSQL += `null`;
      }
    })
    resultSQL += ")"
    // 补,
    if (index < dataList.length - 1) {
      resultSQL += ",";
    }
  });
  resultSQL += ";";

  return {
    resultSQL,
    invokeTree: "", // 取第一个作为根节点
  };
}

/**
 * 生成空间 Sql     空间对象 点 => [[22,66],[77,88]]   多边形 [[1,2],[2,4],[5,6],[7,8],[1,2]]
 * @param json
 */
function doGenerateGeometrySQL(json: any,) {
  console.log("生成空间SQL语句");
  // 先获取对象的key
  const keys = Object.keys(json);
  if (isEmpty(keys)) {
    return { error: "空对象！！" }
  }
  let resultSQL = "";
  // 遍历对象的值
  keys.forEach(key => {
    let value = json[key];
    // 只筛选出符合格式的空间对象数据 暂时只考虑 点 和多边形
    if (!isEmpty(value) && value instanceof Array && value.length > 1) {
      // 此时为点
      if (value.length == 2 && !(value[0] instanceof Array)) {
        // 拼接生成点的语句
        resultSQL = `ST_GeomFromText('POINT(${value[0]} ${value[1]})',4326,'axis-order=long-lat')`
      }
      // 此时为多边形
      if (value[0] instanceof Array && value.length >= 3) {
        // 拼接生成多边形的语句
        let polygon = "ST_GeomFromText('POLYGON ((";
        value.forEach(point => {
          polygon += `${point[0]},${point[1]}`;
        });
        polygon += "))',4326,'axis-order=long-lat')";
        if (resultSQL.length>0) {
          resultSQL+=","+polygon
        }
      }
    }
  });

  return {
    resultSQL,
    invokeTree: "", // 取第一个作为根节点
  };

}

/**
 * 字段校验
 * @param obj 需要校验的对象
 * @param fields 需要检验的字段数组 ['name','age']
 * @returns 
 */
function fieldCheck(obj: any, fields: string[]) {
  let error;
  if (!isEmpty(fields)) {
    for (let field of fields) {
      if (isEmpty(obj[field])) {
        error = `${field}不能为空！！`;
      }
    }
  }
  return error;
}

/**
 * 递归生成 SQL
 * @param key
 * @param context
 * @param params
 * @param invokeTreeNode
 */
function generateSQL(
  key: string,
  context: InputJSON,
  params?: Record<string, string>,
  invokeTreeNode?: InvokeTreeNode
): string {
  const currentNode = context[key];
  if (!currentNode) {
    return "";
  }
  let childInvokeTreeNode: InvokeTreeNode | undefined;
  if (invokeTreeNode) {
    childInvokeTreeNode = {
      title: key,
      sql: currentNode.sql ?? currentNode,
      params,
      children: [],
    };
    invokeTreeNode.children?.push(childInvokeTreeNode);
  }
  const result = replaceParams(
    currentNode,
    context,
    params,
    childInvokeTreeNode
  );
  const resultSQL = replaceSubSql(result, context, childInvokeTreeNode);
  if (childInvokeTreeNode) {
    childInvokeTreeNode.resultSQL = resultSQL;
  }
  return resultSQL;
}

/**
 * 参数替换（params）
 * @param currentNode
 * @param context
 * @param params 动态参数
 * @param invokeTreeNode
 */
function replaceParams(
  currentNode: InputJSONValue,
  context: InputJSON,
  params?: Record<string, string>,
  invokeTreeNode?: InvokeTreeNode
): string {
  if (currentNode == null) {
    return "";
  }
  const sql = currentNode.sql ?? currentNode;
  if (!sql) {
    return "";
  }
  // 动态、静态参数结合，且优先用静态参数
  params = { ...(params ?? {}), ...currentNode.params };
  if (invokeTreeNode) {
    invokeTreeNode.params = params;
  }
  // 无需替换
  if (!params || Object.keys(params).length < 1) {
    return sql;
  }
  let result = sql;
  for (const paramsKey in params) {
    const replacedKey = `#{${paramsKey}}`;
    // 递归解析
    // const replacement = replaceSubSql(
    //   params[paramsKey],
    //   context,
    //   invokeTreeNode
    // );
    const replacement = params[paramsKey];
    // find and replace
    result = result.replaceAll(replacedKey, replacement);
  }
  return result;
}
/**
 * 判断对象是否为空
 * @param obj 
 * @returns 
 */
function isEmpty(obj: any) {
  // 数字判空
  if (isNumber(obj)) {
    return false;
  }
  // 字符串和数组判空
  if (obj && obj.length > 0) {
    return false;
  }
  return true;
}

/**
 * 判断是否为数字
 * @param obj 
 * @returns 
 */
function isNumber(obj: any) {
  if (parseFloat(obj).toString() == "NaN") {
    return false;
  }
  return true;
}

/**
 * 替换子 SQL（@xxx）
 * @param sql
 * @param context
 * @param invokeTreeNode
 */
function replaceSubSql(
  sql: string,
  context: InputJSON,
  invokeTreeNode?: InvokeTreeNode
): string {
  if (!sql) {
    return "";
  }
  let result = sql;
  result = String(result);
  let regExpMatchArray = matchSubQuery(result);
  // 依次替换
  while (regExpMatchArray && regExpMatchArray.length > 2) {
    // 找到结果
    const subKey = regExpMatchArray[1];
    // 可用来替换的节点
    const replacementNode = context[subKey];
    // 没有可替换的节点
    if (!replacementNode) {
      const errorMsg = `${subKey} 不存在`;
      alert(errorMsg);
      throw new Error(errorMsg);
    }
    // 获取要传递的动态参数
    // e.g. "a = b, c = d"
    let paramsStr = regExpMatchArray[2];
    if (paramsStr) {
      paramsStr = paramsStr.trim();
    }
    // e.g. ["a = b", "c = d"]
    const singleParamsStrArray = paramsStr.split("|||");
    // string => object
    const params: Record<string, string> = {};
    for (const singleParamsStr of singleParamsStrArray) {
      // 必须分成 2 段
      const keyValueArray = singleParamsStr.split("=", 2);
      if (keyValueArray.length < 2) {
        continue;
      }
      const key = keyValueArray[0].trim();
      params[key] = keyValueArray[1].trim();
    }
    // 递归解析被替换节点
    const replacement = generateSQL(subKey, context, params, invokeTreeNode);
    result = result.replace(regExpMatchArray[0], replacement);
    regExpMatchArray = matchSubQuery(result);
  }
  return result;
}

/**
 * 匹配子查询
 * @param str
 */
function matchSubQuery(str: string) {
  if (!str) {
    return null;
  }
  const regExp = /@([\u4e00-\u9fa5_a-zA-Z0-9]+)\((.*?)\)/;
  let regExpMatchArray = str.match(regExp);
  if (!regExpMatchArray || regExpMatchArray.index === undefined) {
    return null;
  }
  // @ 开始位置
  let startPos = regExpMatchArray.index;
  // 左括号右侧
  let leftParenthesisPos = startPos + regExpMatchArray[1].length + 2;
  // 遍历游标
  let currPos = leftParenthesisPos;
  // 默认匹配结束位置，需要对此结果进行修正
  let endPos = startPos + regExpMatchArray[0].length;
  // 剩余待匹配左括号数量
  let leftCount = 1;
  while (currPos < str.length) {
    const currentChar = str.charAt(currPos);
    if (currentChar === "(") {
      leftCount++;
    } else if (currentChar === ")") {
      leftCount--;
    }
    // 匹配结束
    if (leftCount == 0) {
      endPos = currPos + 1;
      break;
    }
    currPos++;
  }
  return [
    str.slice(startPos, endPos),
    regExpMatchArray[1],
    str.slice(leftParenthesisPos, endPos - 1),
  ];
}
